package com.xinchuang.util;

import lombok.extern.slf4j.Slf4j;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

/**
 * @ClassName DButils
 * @Description: TODO(外部数据库操作工具)
 * @Author zhangxuan
 * @Date 2021-04-19 17:07
 * @Verion V1.0
 */
@Slf4j
public class DButils<T> {

    public Connection getCon() {
        Connection connection = null;
        try {
            Class.forName("org.sqlite.JDBC");
            connection = DriverManager.getConnection("jdbc:sqlite:log-sqlite.db");
            return connection;
        } catch (ClassNotFoundException | SQLException e) {
            log.error("数据连接获取异常:" + e.getMessage(), e);
        }
        return null;
    }

    public boolean createTable(Connection connection, String tableName, List<String> fieldList) {
        StringBuffer sb = new StringBuffer("CREATE TABLE IF NOT EXISTS ");
        sb.append(tableName + " (");
        for (int i = 0; i < fieldList.size(); i++) {
            if (i == fieldList.size() - 1) {
                sb.append(fieldList.get(i) + ")");
            } else {
                sb.append(fieldList.get(i) + ",");
            }
        }
        try {
            PreparedStatement ps = connection.prepareStatement(sb.toString());
            ps.execute();
            ps.close();
            return true;
        } catch (SQLException sqlException) {
            log.error("创建数据库表单异常" + sqlException.getMessage(), sqlException);
        } finally {
            sb.setLength(0);
        }
        return false;
    }

    public boolean insertTable(Connection connection, String tableName, List<String> fieldList, List<Object> valueList) throws SQLException {
        StringBuffer sb = new StringBuffer("INSERT OR REPLACE INTO " + tableName);
        for (int i = 0; i < fieldList.size(); i++) {
            if (i == 0) {
                sb.append(" (" + fieldList.get(i) + ",");
            } else if (i == fieldList.size() - 1) {
                sb.append(fieldList.get(i) + ")");
            } else {
                sb.append(fieldList.get(i));
            }
        }
        sb.append(" VALUES");
        for (int i = 0; i < fieldList.size(); i++) {
            if (i == 0) {
                sb.append(" (?,");
            } else if (i == fieldList.size() - 1) {
                sb.append("?)");
            } else {
                sb.append("?,");
            }
        }
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sb.toString());
            for (int i = 1; i <= valueList.size(); i++) {
                ps.setObject(i, valueList.get(i - 1));
            }
            if (ps.executeUpdate() > 0) {
                return true;
            }
        } finally {
            if (null != ps) {
                ps.close();
            }
            sb.setLength(0);
        }
        return false;
    }

    public boolean deleteTable(Connection connection, String tableName, List<String> fieldList, List<Object> valueList) throws SQLException {
        StringBuffer sb = new StringBuffer("DELETE FROM " + tableName);
        for (int i = 0; i < fieldList.size(); i++) {
            if (i == 0) {
                sb.append(" WHERE " + fieldList.get(i) + " = ?");
            } else {
                sb.append(" AND " + fieldList.get(i) + " = ?");
            }
        }
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sb.toString());
            for (int i = 1; i <= valueList.size(); i++) {
                ps.setObject(i, valueList.get(i - 1));
            }
            if (ps.executeUpdate() > 0) {
                return true;
            }
        } finally {
            if (null != ps) {
                ps.close();
            }
            sb.setLength(0);
        }
        return false;
    }

}